﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CodeGenerator.Interface;
using System.Data;
using System.Data.SqlClient;

namespace CodeGenerator.Control
{
    class CGetDatabaseInfoSqlServer2005 : IGetDatabaseInfo
    {
        #region IGetDatabaseInfo 成员

        /// <summary>
        /// 获取指定数据库的所有表名
        /// </summary>
        /// <param name="database">数据库名</param>
        /// <param name="obj">数据库连接对象</param>
        /// <returns>表集合</returns>
        public DataTable GetTableName(string database, SqlConnection conn)
        {
            //查询指定数据库所有表名的SQL语句
            //string sqlStr = "Select Name From " + database + "..SysObjects Where XType='U' ORDER BY Name";
            string sqlStr = "select * from " + database + ".INFORMATION_SCHEMA.TABLES order by TABLE_NAME";
            //尝试打开数据库，并读取所有表名
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adp = new SqlDataAdapter();
            cmd.CommandText = sqlStr;
            cmd.Connection = conn;
            conn.Open();
            adp.SelectCommand = cmd;
            adp.Fill(dt);
            //释放资源
            conn.Close();
            adp.Dispose();
            cmd.Dispose();
            //返回表名集合
            return dt;
        }
        /// <summary>
        /// 获取指定表的所有字段名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="conn">数据库连接对象</param>
        /// <returns>字段集合</returns>
        public DataTable GetField(string tableName, SqlConnection conn)
        {
            //查询指定表所有字段名的SQL语句
            string sqlStr = "Select s1.Name,s2.name FROM SysColumns as s1,systypes as s2 Where id=Object_Id('" + tableName + "') and s1.xtype=s2.xtype and s2.name<>'sysname'";
            //尝试打开数据库，并读取所有字段名
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adp = new SqlDataAdapter();
            cmd.CommandText = sqlStr;
            cmd.Connection = conn;
            conn.Open();
            adp.SelectCommand = cmd;
            adp.Fill(dt);
            //释放资源
            conn.Close();
            adp.Dispose();
            cmd.Dispose();
            //返回表名集合
            return dt;
        }

        /// <summary>
        /// 获取指定数据库和表的所有字段名
        /// </summary>
        /// <param name="database"></param>
        /// <param name="tableName"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public DataTable GetField(string database, string tablename, SqlConnection conn) 
        {
            //查询指定表所有字段名的SQL语句
            string sqlStr = "select TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION from " + database + ".INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tablename + "' order by TABLE_NAME;";
            //尝试打开数据库，并读取所有字段名
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adp = new SqlDataAdapter();
            cmd.CommandText = sqlStr;
            cmd.Connection = conn;
            conn.Open();
            adp.SelectCommand = cmd;
            adp.Fill(dt);
            //释放资源
            conn.Close();
            adp.Dispose();
            cmd.Dispose();
            //返回表名集合
            return dt;
        }

        #endregion
    }
}
